#!/usr/bin/perl -w
use strict;
use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use cluster_from_pairs;
use parse_bl2seq;
use chromosomes;
use DBI;
use DBD::Pg;
use linewrap;
use Spreadsheet::WriteExcel::Big;
use dbh_helper;

(my $my_dir = $FindBin::Bin) =~ s/(.*)\/$/$1/;

print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    xls_protocols_syntenic_block_sizes

	Description:

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#-----------------------------------------------------------------------------------------
#	Get DB parameters
#
use db_parameters;
use ortho_species;
#my @db_param = get_db_parameters();
#
#	Get DB parameters
#-----------------------------------------------------------------------------------------




#_________________________________________________________________________________________

#	get_xls_formats

#_________________________________________________________________________________________
sub get_xls_formats(\%$)
{
	my ($formats,  $workbook) = @_;
	# bold
	$formats->{bold} = $workbook->addformat(bold => 1, color => 'red');
	
	# blue and bold
	$formats->{blue_bold} = $workbook->addformat(bold => 1, color => 'blue');
	
	# red and bold
	$formats->{red_bold} = $workbook->addformat	(
															color => 'red',
															bold  => 1,
														);

	# red and bold and large
	$formats->{red_bold_large} = $workbook->addformat	(
															font  => 'Times New Roman',
															size  => 14,
															color => 'red',
															bold  => 1,
															bg_color => 'yellow',
														);

	# red and bold on yellow background
	$formats->{red_bold_yellow} = $workbook->addformat(bold		=> 1, 
													   color	=> 'red',
													   bg_color	=> 'yellow');
	
	# three significant digits
	$formats->{double_digit} = $workbook->addformat();
	$formats->{double_digit}->set_num_format("0.00");
	
	# percentage
	$formats->{percent} = $workbook->addformat();
	$formats->{percent}->set_num_format("0%");
	
	# percentage
	$formats->{blue_bold_percent} = $workbook->addformat(bold => 1, color => 'blue');
	$formats->{blue_bold_percent}->set_num_format("0%");

	# normal
	$formats->{black} = $workbook->addformat();
}



#_________________________________________________________________________________________

#	do_select_n50

#		get size of syntenic blocks accounting for half of more of all genes
#			in a protocol

#_________________________________________________________________________________________
sub do_select_n50($)
{
	# new
	my ($dbh) = @_;
	print STDERR "Get median block sizes per protocol...\n";
	my $sql_cmd = <<"PL/SQLCMD";
    SELECT
            analysis_set, species, median_block_size
        FROM 
            orthologs.micro_syntenic_block_size_summary
        WHERE
            protocol_id = $curr_protocol_id;
PL/SQLCMD
	my $data = $dbh->selectall_arrayref($sql_cmd);
	my %return_data;
	for my $line(@$data)
	{
		$return_data{$line->[0]}{$line->[1]} = $line->[2];
	}
	return %return_data;
}




#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
#
#	Get data from panda
#
#
my $dbh = connect_to_panda();

system ("mkdir -p $dir_orthologs_output/excel") and die;
my $workbook  = Spreadsheet::WriteExcel::Big->new("$dir_orthologs_output/excel/syntenic_block_n50_sizes.xls");
die "Problems creating new Excel file: $!" unless defined $workbook;

my %formats;
get_xls_formats(%formats, $workbook);

	
my %n50 = do_select_n50($dbh);
my $worksheet = $workbook->addworksheet("N50");
$worksheet->set_zoom(150);

# make sure first column wide enough
$worksheet->set_column(1, 1,  40);

# write explanation
my $row = 1;
$worksheet->write_row($row++ ,1, 
						["N50: Syntenic blocks size ".
						"accounting for > 50% of genes per protocol", '','','','',''],
									$formats{red_bold_large});

# header
$worksheet->write(++$row, 1, "Analysis", $formats{red_bold});
$worksheet->write_row($row, 2, [$ortho_name1, $ortho_name2], $formats{blue_bold});
my @analyses = qw(all  1_to_1);
my %analysis_descriptions =
	(
	 'all'	            			=> 'All orthologues',
	 '1_to_1'						=> '1:1s only');

for my $analysis(@analyses)
{
	$worksheet->write(++$row, 1, $analysis_descriptions{$analysis}, $formats{blue_bold});
	$worksheet->write($row, 2, $n50{$analysis}{$ortho_name1});
	$worksheet->write($row, 3, $n50{$analysis}{$ortho_name2});
}



$workbook->close();
print STDERR "Finished.\n";

#SELECT 
#block_size
#FROM
#???
#orthologs.micro_syntenic_blocks USING (protocol_id, gene_id) JOIN
#orthologs.micro_syntenic_block_sizes USING (micro_syntenic_block_id, species, protocol_id);
#
#
#SELECT 
#closest_neighbour_dist,
#other_dir_neighbour_dist
#FROM
#orthologs.syntenic_distances JOIN
#??? USING (protocol_id, gene_id);
